﻿Namespace Kasa
	Module Functions

		Function KasaDevir(ByVal Process As Token, ByVal Yasa As String, ByVal Tarih As Date) As Decimal
			Return Process.OpenSQL("select top 1 bakiye from kasabakiye where tarih <= @tarih order by tarih desc", Tarih)(0)
		End Function

		Function KasaOzeti(ByVal Process As Token, ByVal Yasa As String, ByVal Tarih1 As Date, ByVal Tarih2 As Date, HesapNo As String) As Recordset
			Q("select ltrim(a.hesappno) as hesapno, b.acik,sum(borc) as sborc,sum(alacak) as salacak")
			Q("from kmahsup a inner join hesapplan b on a.hesappno = b.hesapno")
			Q("where islemtar between @tarih1 and @tarih2")
			If HesapNo <> "" Then Q("and hesapno = '" & HesapNo & "'")
			Limit(Process, Yasa)
			Q("group by a.hesappno, b.acik")
			Q("order by a.hesappno, b.acik")
			Return Process.OpenSQL("*", Tarih1, Tarih2)
		End Function

		Sub Limit(ByVal Process As Token, ByVal Yasa As String)
			If Yasa = "1" Then
				Q("and hesappno not like '1__5%'")
				Q("and hesappno not like '2__5%'")
				Q("and hesappno not like '3__5%'")
				Q("and hesappno not like '4__5%'")
				Q("and hesappno not like '5__5%'")
				Q("and hesappno not like '601%'")
				Q("and hesappno not like '665%'")
				Q("and hesappno not like '67_5%'")
			ElseIf Yasa = "2" Then
				Q("and hesappno not like '1__0%'")
				Q("and hesappno not like '2__0%'")
				Q("and hesappno not like '3__0%'")
				Q("and hesappno not like '4__0%'")
				Q("and hesappno not like '5__0%'")
				Q("and hesappno not like '600%'")
				Q("and hesappno not like '664%'")
				Q("and hesappno not like '67_0%'")
			End If
		End Sub

		Function KasaDokumu(ByVal Process As Token, ByVal Yasa As String, ByVal Tarih1 As Date, ByVal Tarih2 As Date, HesapNo As String) As Recordset
			Q("select islemtar as tarih, convert(numeric,tahsilno) as tahfis, convert(numeric,tediyeno) as tedfis, convert(numeric,refno) as emanetfis,")
			Q("kime as adi, hesappno as hesapno, b.acik as hesapadi, borc as tahsilat, alacak as tediye")
			Q("from kmahsup a left join hesapplan b on a.hesappno = b.hesapno")
			Q("where	islemtar between @tarih1 and @tarih2")
			If HesapNo <> "" Then Q("and hesapno = '" & HesapNo & "'")
			Limit(Process, Yasa)
			Return Process.OpenSQL("*", Tarih1, Tarih2)
		End Function

		Function OdemeOzeti(ByVal Process As Token, ByVal Tarih1 As Date, ByVal Tarih2 As Date) As Recordset
			Q("select count(hesappno) as adet,hesappno,b.acik,sum(borc) as tahsilat")
			Q("from kodeme a left join hesapplan b on a.hesappno=b.hesapno")
			Q("where islemtar between :tarih1 and :tarih2 group by hesappno,b.acik order by hesappno")
			Return Process.OpenSQL("*", Tarih1, Tarih2)
		End Function

		Function OdemeListesi(ByVal Process As Token, ByVal Tarih1 As Date, ByVal Tarih2 As Date) As Recordset
			Q("select fisno,hesappno,borc,islemtar,adi from kodeme")
			Q("where borc is not null and islemtar between :tarih1 and :tarih2 order by convert(numeric,fisno)")
			Return Process.OpenSQL("*", Tarih1, Tarih2)
		End Function

		Function EmanetAnaOku(ByVal Process As Token, ByVal Yil As Integer, ByVal Fisno As Integer) As Recordset
			Q("select * from emanet where year(tahsiltar) = @yil and tahsilno = @fisno and makbuz is null order by islemtar,rowid")
			Return Process.OpenSQL("*", Yil, Fisno)
		End Function

		Function EmanetDetayOku(ByVal Process As Token, ByVal Yil As Integer, ByVal Fisno As Integer) As Recordset
			Q("select * from emanet where year(tahsiltar) = @yil and tahsilno = @fisno and makbuz is not null order by islemtar,rowid")
			Return Process.OpenSQL("*", Yil, Fisno)
		End Function

		Function EmanetOku(ByVal Process As Token, ByVal Yil As Integer, ByVal Fisno As Integer) As Recordset
			Q("select * from emanet where year(tahsiltar) = @yil and tahsilno = @fisno order by islemtar,rowid")
			Return Process.OpenSQL("*", Yil, Fisno)
		End Function

		Function KmahsupOku(ByVal Process As Token, ByVal yil As Integer, ByVal fisno As Integer) As Recordset
			Q("select * from kmahsup where tahsilno = @fisno and year(islemtar) = @yil")
			Return Process.OpenSQL("*", fisno, yil)
		End Function

		Function EmanetKalanRaporu(ByVal Process As Token, ByVal Alacakli As String, ByVal Tarih1 As Date, ByVal Tarih2 As Date) As Recordset
			Q("select distinct a.tahsilno,a.tahsiltar,c.kime,a.emanmikt,a.emanmikk,c.hesappno")
			Q("from emanet a inner join")
			Q("(select tahsilno, tahsiltar, max(islemtar) as itar from emanet group by tahsilno, tahsiltar) b")
			Q("on a.tahsilno = b.tahsilno and a.tahsiltar = b.tahsiltar and a.islemtar = b.itar")
			Q("inner join kmahsup c on a.tahsilno = c.tahsilno and c.islemtar = a.tahsiltar")
			Q("and c.kime like @kime") ' and a.emanmikk > 0")
			Q("and a.tahsiltar between @d1 and @d2")
			Q("order by a.tahsiltar")
			Return Process.OpenSQL("*", "%" & Alacakli & "%", Tarih1, Tarih2)
		End Function

	End Module
End Namespace
